// 图库管理模型
const { postgresPool, redisClient } = require('../config/database');

class Gallery {
    constructor(data) {
        this.id = data.id;
        this.name = data.name;
        this.description = data.description;
        this.imageUrl = data.image_url;
        this.thumbnailUrl = data.thumbnail_url;
        this.fileSize = data.file_size;
        this.fileType = data.file_type;
        this.dimensions = data.dimensions;
        this.category = data.category;
        this.tags = data.tags || [];
        this.usageType = data.usage_type; // 'carousel', 'featured', 'about', 'blog', 'product'
        this.isActive = data.is_active;
        this.sortOrder = data.sort_order;
        this.createdBy = data.created_by;
        this.createdAt = data.created_at;
        this.updatedAt = data.updated_at;
    }

    // 获取所有图片
    static async findAll(options = {}) {
        const {
            page = 1,
            limit = 20,
            category = null,
            usageType = null,
            search = null,
            isActive = true
        } = options;

        try {
            const offset = (page - 1) * limit;
            let whereConditions = [];
            let queryParams = [];
            let paramCount = 1;

            if (isActive !== undefined) {
                whereConditions.push(`is_active = $${paramCount}`);
                queryParams.push(isActive);
                paramCount++;
            }

            if (category) {
                whereConditions.push(`category = $${paramCount}`);
                queryParams.push(category);
                paramCount++;
            }

            if (usageType) {
                whereConditions.push(`usage_type = $${paramCount}`);
                queryParams.push(usageType);
                paramCount++;
            }

            if (search) {
                whereConditions.push(`(
                    name ILIKE $${paramCount} OR 
                    description ILIKE $${paramCount} OR
                    tags::text ILIKE $${paramCount}
                )`);
                queryParams.push(`%${search}%`);
                paramCount++;
            }

            const whereClause = whereConditions.length > 0 
                ? `WHERE ${whereConditions.join(' AND ')}` 
                : '';

            // 获取总数
            const countQuery = `SELECT COUNT(*) FROM gallery ${whereClause}`;
            const countResult = await postgresPool.query(countQuery, queryParams);
            const total = parseInt(countResult.rows[0].count);

            // 获取数据
            const dataQuery = `
                SELECT * FROM gallery 
                ${whereClause}
                ORDER BY sort_order ASC, created_at DESC 
                LIMIT $${paramCount} OFFSET $${paramCount + 1}
            `;
            queryParams.push(limit, offset);
            
            const result = await postgresPool.query(dataQuery, queryParams);
            
            return {
                data: result.rows.map(row => new Gallery(row)),
                pagination: {
                    page,
                    limit,
                    total,
                    totalPages: Math.ceil(total / limit)
                }
            };
        } catch (error) {
            console.error('获取图库列表失败:', error);
            throw error;
        }
    }

    // 根据ID获取图片
    static async findById(id) {
        try {
            const result = await postgresPool.query(
                'SELECT * FROM gallery WHERE id = $1',
                [id]
            );
            
            if (result.rows.length === 0) {
                return null;
            }
            
            return new Gallery(result.rows[0]);
        } catch (error) {
            console.error('获取图片详情失败:', error);
            throw error;
        }
    }

    // 创建图片
    static async create(galleryData) {
        try {
            const fields = [
                'name', 'description', 'image_url', 'thumbnail_url', 
                'file_size', 'file_type', 'dimensions', 'category',
                'tags', 'usage_type', 'is_active', 'sort_order', 'created_by'
            ];
            
            const values = fields.map(field => galleryData[field]);
            const placeholders = fields.map((_, index) => `$${index + 1}`);
            
            const query = `
                INSERT INTO gallery (${fields.join(', ')})
                VALUES (${placeholders.join(', ')})
                RETURNING *
            `;
            
            const result = await postgresPool.query(query, values);
            
            // 清除相关缓存
            await redisClient.del('gallery:carousel');
            await redisClient.del('gallery:featured');
            await redisClient.del('gallery:about');
            
            return new Gallery(result.rows[0]);
        } catch (error) {
            console.error('创建图片失败:', error);
            throw error;
        }
    }

    // 更新图片
    static async update(id, updateData) {
        try {
            const fields = Object.keys(updateData);
            const setClause = fields.map((field, index) => `${field} = $${index + 1}`).join(', ');
            const values = [...fields.map(field => updateData[field]), id];
            
            const query = `
                UPDATE gallery 
                SET ${setClause}, updated_at = CURRENT_TIMESTAMP
                WHERE id = $${fields.length + 1}
                RETURNING *
            `;
            
            const result = await postgresPool.query(query, values);
            
            if (result.rows.length === 0) {
                return null;
            }
            
            // 清除相关缓存
            await redisClient.del('gallery:carousel');
            await redisClient.del('gallery:featured');
            await redisClient.del('gallery:about');
            
            return new Gallery(result.rows[0]);
        } catch (error) {
            console.error('更新图片失败:', error);
            throw error;
        }
    }

    // 删除图片
    static async delete(id) {
        try {
            const result = await postgresPool.query(
                'DELETE FROM gallery WHERE id = $1 RETURNING *',
                [id]
            );
            
            if (result.rows.length === 0) {
                return false;
            }
            
            // 清除相关缓存
            await redisClient.del('gallery:carousel');
            await redisClient.del('gallery:featured');
            await redisClient.del('gallery:about');
            
            return true;
        } catch (error) {
            console.error('删除图片失败:', error);
            throw error;
        }
    }

    // 获取特定用途的图片
    static async findByUsageType(usageType, limit = 10) {
        try {
            const cacheKey = `gallery:${usageType}`;
            
            // 尝试从缓存获取
            const cachedData = await redisClient.get(cacheKey);
            if (cachedData) {
                return JSON.parse(cachedData);
            }
            
            const result = await postgresPool.query(
                `SELECT * FROM gallery 
                 WHERE usage_type = $1 AND is_active = true
                 ORDER BY sort_order ASC, created_at DESC
                 LIMIT $2`,
                [usageType, limit]
            );
            
            const galleries = result.rows.map(row => new Gallery(row));
            
            // 缓存结果（5分钟）
            await redisClient.setex(cacheKey, 300, JSON.stringify(galleries));
            
            return galleries;
        } catch (error) {
            console.error(`获取${usageType}图片失败:`, error);
            throw error;
        }
    }

    // 获取分类统计
    static async getCategoryStats() {
        try {
            const result = await postgresPool.query(`
                SELECT 
                    category,
                    COUNT(*) as count,
                    COUNT(CASE WHEN is_active THEN 1 END) as active_count
                FROM gallery 
                GROUP BY category
                ORDER BY count DESC
            `);
            
            return result.rows;
        } catch (error) {
            console.error('获取分类统计失败:', error);
            throw error;
        }
    }

    // 获取使用统计
    static async getUsageStats() {
        try {
            const result = await postgresPool.query(`
                SELECT 
                    usage_type,
                    COUNT(*) as count,
                    COUNT(CASE WHEN is_active THEN 1 END) as active_count
                FROM gallery 
                GROUP BY usage_type
                ORDER BY count DESC
            `);
            
            return result.rows;
        } catch (error) {
            console.error('获取使用统计失败:', error);
            throw error;
        }
    }

    // 批量更新排序
    static async updateSortOrder(sortData) {
        try {
            const client = await postgresPool.connect();
            
            try {
                await client.query('BEGIN');
                
                for (const item of sortData) {
                    await client.query(
                        'UPDATE gallery SET sort_order = $1 WHERE id = $2',
                        [item.sortOrder, item.id]
                    );
                }
                
                await client.query('COMMIT');
                
                // 清除相关缓存
                await redisClient.del('gallery:carousel');
                await redisClient.del('gallery:featured');
                await redisClient.del('gallery:about');
                
                return true;
            } catch (error) {
                await client.query('ROLLBACK');
                throw error;
            } finally {
                client.release();
            }
        } catch (error) {
            console.error('批量更新排序失败:', error);
            throw error;
        }
    }
}

module.exports = Gallery;